﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace Library
{
    public class clReport
    {
        private SqlConnection sqlcon;
        private SqlCommand sqlcmd;
        public static SqlConnection GetConnection()
        {
            string strConnect = ConfigurationManager.ConnectionStrings["StrConnectString"].ToString();
            SqlConnection sqlcon = new SqlConnection(strConnect);
            return sqlcon;
        }

        public DataTable reportCQcophi(string dateview)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_reportCQcophi", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@dateview", SqlDbType.DateTime).Value = dateview;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable reportCQKophi (string dateview)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_reportCQKophi", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@dateview", SqlDbType.DateTime).Value = dateview;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable reportTuGiaDung(string dateview, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_reportAllTuGiaTheoPho", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@dateview", SqlDbType.DateTime).Value = dateview;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable reportTughiTay(string dateview, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_reportAllTuGiaTheoPhoNVGhi", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@dateview", SqlDbType.DateTime).Value = dateview;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable reportDSThayDH(string dateview, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_reportThayDH", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@dateview", SqlDbType.DateTime).Value = dateview;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable baocaoSuDungTang(string dateview, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_baocao_sudungtang", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@dateview", SqlDbType.DateTime).Value = dateview;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable baocaoSuDungGiam(string dateview, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_baocao_sudunggiam", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@dateview", SqlDbType.DateTime).Value = dateview;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable baocaoDH5nam(string dateview, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_baocao_DongHo_5nam", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@dateview", SqlDbType.DateTime).Value = dateview;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable baocaosudung0m3(string dateview, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_baocao_0m3", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@dateview", SqlDbType.DateTime).Value = dateview;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable baocaosudung1den3(string dateview, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_baocao_1den3m3", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@dateview", SqlDbType.DateTime).Value = dateview;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable baocaokhachhangmoi(string dateview, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_baocao_DSkhachhangmoi", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@dateview", SqlDbType.DateTime).Value = dateview;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable BaoCaoNghiNgo(int sonhap, string dateview, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_DSKhachHang_nghingo", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@sonhapvao", SqlDbType.Int).Value = sonhap;
                    sqlcmd.Parameters.Add("@dateview", SqlDbType.DateTime).Value = dateview;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable Baocaonhieuthang0m3(string tuthangnam, string denthangnam, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_baocao_nhieuthang_0m3", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@tuthangnam", SqlDbType.NVarChar,50).Value = tuthangnam;
                    sqlcmd.Parameters.Add("@denthangnam", SqlDbType.NVarChar,50).Value = denthangnam;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable Baocaonhieuthang1den3(string tuthangnam, string denthangnam, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_baocao_nhieuthang_1den3m3", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@tuthangnam", SqlDbType.NVarChar, 50).Value = tuthangnam;
                    sqlcmd.Parameters.Add("@denthangnam", SqlDbType.NVarChar, 50).Value = denthangnam;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable BaoCaoKHMoiNhieuThang(string tuthangnam, string denthangnam, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_baocao_nhieuthang_DSkhachhangmoi", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@startdateview", SqlDbType.NVarChar, 50).Value = tuthangnam;
                    sqlcmd.Parameters.Add("@enddateview", SqlDbType.NVarChar, 50).Value = denthangnam;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable BaoCaoNhieuThangKHThayDH(string tuthangnam, string denthangnam, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_baocao_nhieuthang_ThayDH", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@startdateview", SqlDbType.NVarChar, 50).Value = tuthangnam;
                    sqlcmd.Parameters.Add("@enddateview", SqlDbType.NVarChar, 50).Value = denthangnam;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable BaoCaoNhieuThangKHSuDungNuoc(string tuthangnam, string denthangnam, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_baocao_nhieuthang_khsudung", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@startdateview", SqlDbType.NVarChar, 50).Value = tuthangnam;
                    sqlcmd.Parameters.Add("@enddateview", SqlDbType.NVarChar, 50).Value = denthangnam;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable BaoCaoNhieuThangCQCoPhi(string tuthangnam, string denthangnam, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_baocao_nhieuthang_coquancophi", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@startdateview", SqlDbType.NVarChar, 50).Value = tuthangnam;
                    sqlcmd.Parameters.Add("@enddateview", SqlDbType.NVarChar, 50).Value = denthangnam;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }

        public DataTable BaoCaoNhieuThangCQKoPhi(string tuthangnam, string denthangnam, Int16 idDHT, Int16 idPhuong, Int16 idPho)
        {
            DataTable dt = new DataTable();
            try
            {
                using (sqlcon = GetConnection())
                {
                    sqlcon.Open();
                    sqlcmd = new SqlCommand("st_baocao_nhieuthang_coquankophi", sqlcon);
                    sqlcmd.CommandType = CommandType.StoredProcedure;
                    sqlcmd.CommandTimeout = 180;
                    sqlcmd.Parameters.Add("@startdateview", SqlDbType.NVarChar, 50).Value = tuthangnam;
                    sqlcmd.Parameters.Add("@enddateview", SqlDbType.NVarChar, 50).Value = denthangnam;
                    sqlcmd.Parameters.Add("@idDHT", SqlDbType.SmallInt).Value = idDHT;
                    sqlcmd.Parameters.Add("@idPhuong", SqlDbType.SmallInt).Value = idPhuong;
                    sqlcmd.Parameters.Add("@idPho", SqlDbType.SmallInt).Value = idPho;
                    SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
                    da.Fill(dt);
                    sqlcon.Close();
                }
            }
            catch { }
            finally { sqlcmd.Dispose(); sqlcon.Dispose(); }
            return dt;
        }
    }
}
